package com.letoken.platform.repo.app.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.letoken.platform.repo.app.po.AppFundRanking;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author 作者
 * @since 2023-09-13
 */
public interface AppFundRankingMapper extends BaseMapper<AppFundRanking> {
    //@Select("select  *,(select head_portrait from app_user where id=user_id) headUrl from app_fund_ranking where user_id = ${userId} and DATE(create_time) = DATE(NOW())")
    @Select("select  id, user_id userId,SUM(amount) amount,type,create_time createtime,(select head_portrait from app_user where id=user_id) headUrl from app_fund_ranking where user_id = ${userId} and create_time>#{startTime} and create_time<=#{endTime}")
    AppFundRanking getAppFundRanking(@Param("userId")Integer userId,@Param("startTime")String startTime,@Param("endTime")String endTime);

    @Select("select user_id,SUM(amount) amount,(select head_portrait from app_user where id=user_id) headUrl from app_fund_ranking where user_id = ${userId} and type = 1 and create_time>#{startTime} and create_time<=#{endTime}")
    AppFundRanking getAppFundRankingDescWeek(@Param("userId")Integer userId,@Param("startTime")String startTime,@Param("endTime")String endTime);

    //@Select("select * from app_fund_ranking where create_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND CURDATE() ORDER BY amount desc limit 10")
    @Select("select * from app_fund_ranking where create_time>=#{startTime} and create_time<=#{endTime} ORDER BY amount desc limit 10")
    List<AppFundRanking> getAppFundRankingDesc1(@Param("startTime")String startTime,@Param("endTime")String endTime);


    //@Select("select user_id,SUM(amount) amount,id  from app_fund_ranking where type =1 and   create_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE() GROUP BY user_id ORDER BY SUM(amount) desc limit 10")
    @Select("select user_id,SUM(amount) amount,id  from app_fund_ranking where type =1 and  create_time>=#{startTime} and create_time<=#{endTime}  GROUP BY user_id ORDER BY SUM(amount) desc limit 10")
    List<AppFundRanking> getAppFundRankingDesc7(@Param("startTime")String startTime,@Param("endTime")String endTime);


    @Select("select id, user_id userId,SUM(amount) amount,type,create_time createtime,(select head_portrait from app_user where id=user_id) headUrl from app_fund_ranking where create_time>#{startTime} and create_time<=#{endTime} GROUP BY user_id ORDER BY SUM(amount) desc limit 10")
    List<AppFundRanking> getAppFundRanking1(@Param("startTime")String startTime,@Param("endTime")String endTime);


    @Select("select user_id,SUM(amount) amount,(select head_portrait from app_user where id=user_id) headUrl from app_fund_ranking where type =1 and create_time>#{startTime} and create_time<=#{endTime} GROUP BY user_id ORDER BY SUM(amount) desc  limit 10")
   // @Select("select user_id,SUM(amount) amount,(select head_portrait from app_user where id=user_id) headUrl from app_fund_ranking where type =1 and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <=date(create_time) GROUP BY user_id ORDER BY SUM(amount) desc  limit 10")
    List<AppFundRanking> getAppFundRanking7(@Param("startTime")String startTime,@Param("endTime")String endTime);


    @Select("select id, user_id userId,SUM(amount) amount,type,create_time createtime,(select head_portrait from app_user where id=user_id) headUrl  from app_fund_ranking where create_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND CURDATE() and user_id = #{userId}")
    AppFundRanking yesterdayAppFundRanking1(@Param("userId")Integer userId);


    @Select("SELECT SUM(amount) amount,user_id userId,(select head_portrait from app_user where id=user_id) headUrl  FROM app_fund_ranking  WHERE type = 2 AND DATE(create_time) <(SELECT DATE( create_time ) FROM app_ranking_detail WHERE type = 2 ORDER BY create_time DESC LIMIT 1 ) and  DATE(create_time) >= DATE_SUB((SELECT DATE( create_time ) FROM app_ranking_detail WHERE type = 2 ORDER BY create_time DESC LIMIT 1 ),INTERVAL 7 DAY) and user_id=#{userId} GROUP BY user_id")
    AppFundRanking yesterdayAppFundRanking7(@Param("userId")Integer userId);


    @Select("SELECT CONCAT(DATE_ADD(DATE(create_time),INTERVAL 8 DAY), ' 18:00:00') FROM app_fund_ranking WHERE type = 2 ORDER BY create_time DESC LIMIT 1")
    String next7();


    @Select("SELECT CONCAT(DATE_ADD(DATE(create_time),INTERVAL 1 DAY), ' 00:00:00') FROM app_fund_ranking ORDER BY create_time DESC LIMIT 1")
    String next1();


    @Select("select create_time from app_ranking_detail where type =#{type} ORDER BY create_time desc limit 1")
    LocalDateTime time(@Param("type")Integer type);
}
